﻿using BCCommon;
using BCCommon.OAEnums;
using BCEntity.OA.TeamWorks.TaskRecord;
using BCEntity.OA.TeamWorks.TaskRecordCount;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

namespace BCData.OA.TeamWorks.FMISTaskRecord
{
    public class FMISTaskRecordData : IFMISTaskRecordData
    {
        private const string QUERY = @"SELECT A.TaskRecordId,A.WorkflowActivityInstanceId,A.BusinessTypeName,A.BusinessType,A.TaskType,A.TaskTypeName,A.ReceivableAmount,A.PayableAmount,A.ActualReceiptAmount,A.ActualPaidAmount,
                                            A.IsException,A.ExceptionDescription,A.HandledRemark,A.SaleContractNo,A.PurchaseContractNo,A.SaleUniqueCode,B.TaskRecordNo,A.CompanyId,
                                            B.TaskCategory,B.HandleStatus,B.PauseTime,B.PauseRemark,B.RelevantTaskRecordNo,B.CurrentlyStep,B.CreateTime,B.TaskExecuterAdminId,
                                            B.TaskExecuterFullName,B.StartTime,B.CompleteTime,B.TaskRemark,C.StepType,A.PurchaseUniqueCode,A.CapitalPoolWithdrawId,A.CapitalDeductionAmount,
                                            A.DepositDeductionAmount,A.RebateDeductionAmount,A.IsAllDeducted
                                        FROM FMIS_TaskRecord A
                                        INNER JOIN OA_TaskRecord B ON A.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId
                                        INNER JOIN OA_WorkflowActivityStepInstance C ON C.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId AND C.TaskStep=B.CurrentlyStep";
        private const string QUERY_LIST = @"SELECT A.TaskRecordId,A.WorkflowActivityInstanceId,A.BusinessTypeName,A.BusinessType,A.TaskType,A.TaskTypeName,A.ReceivableAmount,A.PayableAmount,A.ActualReceiptAmount,A.ActualPaidAmount,
                                            A.IsException,A.ExceptionDescription,A.HandledRemark,A.SaleContractNo,A.PurchaseContractNo,A.SaleUniqueCode,B.TaskRecordNo,A.CompanyId,
                                            B.TaskCategory,B.HandleStatus,B.PauseTime,B.PauseRemark,B.RelevantTaskRecordNo,B.CurrentlyStep,B.CreateTime,B.TaskExecuterAdminId,
                                            B.TaskExecuterFullName,B.StartTime,B.CompleteTime,B.TaskRemark,C.StepType,A.PurchaseUniqueCode,A.CapitalPoolWithdrawId,A.CapitalDeductionAmount,  
                                            A.DepositDeductionAmount,A.RebateDeductionAmount,A.IsAllDeducted                                        
                                        FROM FMIS_TaskRecord A
                                        INNER JOIN OA_TaskRecord B ON A.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId
                                        INNER JOIN OA_WorkflowActivityStepInstance C ON C.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId";
        private const string QUERY_ENTITY = @"SELECT A.TaskRecordId,A.WorkflowActivityInstanceId,A.BusinessTypeName,A.BusinessType,A.TaskType,A.TaskTypeName,A.ReceivableAmount,A.PayableAmount,A.ActualReceiptAmount,A.ActualPaidAmount,
                                            A.IsException,A.ExceptionDescription,A.HandledRemark,A.SaleContractNo,A.PurchaseContractNo,A.SaleUniqueCode,A.CompanyId,A.PurchaseUniqueCode,A.CapitalPoolWithdrawId,A.CapitalDeductionAmount,
                                            A.DepositDeductionAmount,A.RebateDeductionAmount,A.IsAllDeducted  
                                        FROM FMIS_TaskRecord A";
        
        private readonly ISqlCommand sqlCommand;
        private readonly IDatabaseContext database;

        public FMISTaskRecordData(ISqlCommand sqlCommand,
            IDatabaseContext database)
        {
            this.sqlCommand = sqlCommand;
            this.database = database;
        }

        #region 任务脚本
        /// <summary>
        /// 获取添加任务记录脚本
        /// </summary>
        /// <param name="entity">财务系统任务记录信息</param>
        /// <param name="dynamicParameters">参数列表</param>
        /// <returns></returns>
        private string[] GetInsertTaskRecordSql(FMISTaskRecordEntity entity, ref List<DynamicParameters> dynamicParameters)
        {
            List<string> sqls = new List<string>();
            string oaSql = this.sqlCommand.GetInsertSql("OA_TaskRecord", new string[] { "WorkflowActivityInstanceId","TaskRecordNo","TaskRemark", "TaskCategory", "BusinessType", "BusinessTypeName", "HandleStatus","CompanyId",
                "RelevantTaskRecordNo","CurrentlyStep","CreateTime"});
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkflowActivityInstanceId", entity.WorkflowActivityInstanceId, DbType.Int64, size: 20);
            parameters.Add("?TaskRecordNo", entity.TaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?TaskRemark", entity.TaskRemark, DbType.String);
            parameters.Add("?TaskCategory", entity.TaskCategory.ToString(), DbType.AnsiString);
            parameters.Add("?BusinessType", entity.BusinessType.ToString(), DbType.AnsiString, size: 150);
            parameters.Add("?BusinessTypeName", entity.BusinessTypeName, DbType.AnsiString, size: 50);
            parameters.Add("?HandleStatus", entity.HandleStatus.ToString(), DbType.AnsiString);
            parameters.Add("?RelevantTaskRecordNo", entity.RelevantTaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?CurrentlyStep", entity.CurrentlyStep, DbType.Int32, size: 10);
            parameters.Add("?CreateTime", entity.CurrentlyStep == 0 ? DateTime.Now.AddSeconds(-2) : DateTime.Now, DbType.DateTime);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int32, size: 10);
            sqls.Add(oaSql);
            dynamicParameters.Add(parameters);

            string fmisSql = this.sqlCommand.GetInsertSql("FMIS_TaskRecord", new string[]
            {
                "WorkflowActivityInstanceId",
                "BusinessTypeName",
                "BusinessType",
                "TaskType",
                "TaskTypeName",
                "ReceivableAmount",
                "PayableAmount",
                "SaleContractNo",
                "PurchaseContractNo",
                "SaleUniqueCode",
                "PurchaseUniqueCode",
                "CapitalPoolWithdrawId",
                "CompanyId",
                "CapitalDeductionAmount",
                "DepositDeductionAmount",
                "RebateDeductionAmount",
                "IsAllDeducted"
            });
            
            parameters = new DynamicParameters();
            parameters.Add("?WorkflowActivityInstanceId", entity.WorkflowActivityInstanceId, DbType.Int64, size: 20);
            parameters.Add("?BusinessType", entity.BusinessType.ToString(), DbType.AnsiString, size: 150);
            parameters.Add("?BusinessTypeName", entity.BusinessTypeName, DbType.AnsiString, size: 50);
            parameters.Add("?TaskType", entity.TaskType.ToString(), DbType.AnsiString);
            parameters.Add("?TaskTypeName", entity.TaskTypeName, DbType.AnsiString, size: 50);
            parameters.Add("?ReceivableAmount", entity.ReceivableAmount, DbType.Decimal);
            parameters.Add("?PayableAmount", entity.PayableAmount, DbType.Decimal);
            parameters.Add("?SaleContractNo", entity.SaleContractNo, DbType.AnsiString, size: 50);
            parameters.Add("?PurchaseContractNo", entity.PurchaseContractNo, DbType.AnsiString, size: 50);
            parameters.Add("?SaleUniqueCode", entity.SaleUniqueCode, DbType.AnsiString, size: 50);
            parameters.Add("?PurchaseUniqueCode", entity.PurchaseUniqueCode, DbType.AnsiString, size: 50);
            parameters.Add("?CapitalPoolWithdrawId", entity.CapitalPoolWithdrawId, DbType.Int64);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int32, size: 10);
            parameters.Add("?CapitalDeductionAmount", entity.CapitalDeductionAmount, DbType.Decimal);
            parameters.Add("?DepositDeductionAmount", entity.DepositDeductionAmount, DbType.Decimal);
            parameters.Add("?RebateDeductionAmount", entity.RebateDeductionAmount, DbType.Decimal);
            parameters.Add("?IsAllDeducted", entity.IsAllDeducted, DbType.Boolean);
            sqls.Add(fmisSql);
            dynamicParameters.Add(parameters);
            return sqls.ToArray();
        }

        /// <summary>
        /// 获取添加任务参与者记录脚本
        /// </summary>
        /// <param name="taskReceiverRecordEntities">任务参与者记录列表</param>
        /// <param name="dynamicParameters">参数列表</param>
        /// <returns></returns>
        private string[] GetInsertTaskReceiverRecordSql(List<TaskReceiverRecordEntity> taskReceiverRecordEntities, ref List<DynamicParameters> dynamicParameters)
        {
            List<string> sqls = new List<string>();
            string sql = this.sqlCommand.GetInsertSql("OA_TaskReceiverRecord", new string[] { "ReceiverAdminId", "ReceiverAdminFullName", "ReceiverRole", "TaskStep", "WorkflowActivityInstanceId" });
            foreach (var receiver in taskReceiverRecordEntities)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters = new DynamicParameters();
                parameters.Add("?ReceiverAdminId", receiver.ReceiverAdminId, DbType.Int64);
                parameters.Add("?ReceiverAdminFullName", receiver.ReceiverAdminFullName, DbType.AnsiString, size: 20);
                parameters.Add("?ReceiverRole", receiver.ReceiverRole.ToString(), DbType.AnsiString);
                parameters.Add("?TaskStep", receiver.TaskStep, DbType.Int32);
                parameters.Add("?WorkflowActivityInstanceId", receiver.WorkflowActivityInstanceId, DbType.Int64);
                sqls.Add(sql);
                dynamicParameters.Add(parameters);
            }
            return sqls.ToArray();
        }
        #endregion

        public async Task<bool> Add(FMISTaskRecordEntity fMISTaskRecordEntity)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> parameters = new List<DynamicParameters>();
            sqls.AddRange(this.GetInsertTaskRecordSql(fMISTaskRecordEntity, ref parameters));
            //sqls.AddRange(this.GetInsertTaskReceiverRecordSql(fMISTaskRecordEntity.Receivers, ref parameters));
            return await this.sqlCommand.TransactionExecuteAsync(sqls, parameters) > 0;
        }

        public async Task AddFmisTaskRecordAsync(FMISTaskRecordEntity fmisTaskRecordEntity)
        {
            string sql = this.sqlCommand.GetInsertSql("FMIS_TaskRecord", new string[] {
                "WorkflowActivityInstanceId",
                "BusinessType",
                "BusinessTypeName",
                "TaskType",
                "TaskTypeName",
                "ReceivableAmount",
                "PayableAmount",
                "SaleContractNo",
                "PurchaseContractNo",
                "SaleUniqueCode",
                "PurchaseUniqueCode",
                "CapitalPoolWithdrawId",
                "CompanyId"
            });

            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?WorkflowActivityInstanceId", fmisTaskRecordEntity.WorkflowActivityInstanceId, DbType.Int64, size: 20);
            parameters.Add("?BusinessType", fmisTaskRecordEntity.BusinessType.ToString(), DbType.AnsiString, size: 150);
            parameters.Add("?BusinessTypeName", fmisTaskRecordEntity.BusinessTypeName, DbType.AnsiString, size: 50);
            parameters.Add("?TaskType", fmisTaskRecordEntity.TaskType.ToString(), DbType.AnsiString);
            parameters.Add("?TaskTypeName", fmisTaskRecordEntity.TaskTypeName, DbType.AnsiString, size: 50);
            parameters.Add("?ReceivableAmount", fmisTaskRecordEntity.ReceivableAmount, DbType.Decimal);
            parameters.Add("?PayableAmount", fmisTaskRecordEntity.PayableAmount, DbType.Decimal);
            parameters.Add("?SaleContractNo", fmisTaskRecordEntity.SaleContractNo, DbType.AnsiString, size: 50);
            parameters.Add("?PurchaseContractNo", fmisTaskRecordEntity.PurchaseContractNo, DbType.AnsiString, size: 50);
            parameters.Add("?SaleUniqueCode", fmisTaskRecordEntity.SaleUniqueCode, DbType.AnsiString, size: 50);
            parameters.Add("?PurchaseUniqueCode", fmisTaskRecordEntity.PurchaseUniqueCode, DbType.AnsiString, size: 50);
            parameters.Add("?CapitalPoolWithdrawId", fmisTaskRecordEntity.CapitalPoolWithdrawId, DbType.Int64);
            parameters.Add("?CompanyId", fmisTaskRecordEntity.CompanyId, DbType.Int32, size: 10);

            await this.sqlCommand.ExecuteAsync(sql, parameters);
        }

        public async Task<bool> Append(FMISTaskRecordEntity fMISTaskRecordEntity)
        {
            string sql = this.sqlCommand.GetInsertSql("OA_TaskRecord", new string[] { "WorkflowActivityInstanceId","TaskRecordNo","TaskRemark", "TaskCategory", "BusinessType", "BusinessTypeName", "HandleStatus","CompanyId",
                "RelevantTaskRecordNo","CurrentlyStep"});
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkflowActivityInstanceId", fMISTaskRecordEntity.WorkflowActivityInstanceId, DbType.Int64, size: 20);
            parameters.Add("?TaskRecordNo", fMISTaskRecordEntity.TaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?TaskRemark", fMISTaskRecordEntity.TaskRemark, DbType.String);
            parameters.Add("?TaskCategory", fMISTaskRecordEntity.TaskCategory.ToString(), DbType.AnsiString);
            parameters.Add("?BusinessType", fMISTaskRecordEntity.BusinessType.ToString(), DbType.AnsiString, size: 150);
            parameters.Add("?BusinessTypeName", fMISTaskRecordEntity.BusinessTypeName, DbType.AnsiString, size: 50);
            parameters.Add("?HandleStatus", fMISTaskRecordEntity.HandleStatus.ToString(), DbType.AnsiString);
            parameters.Add("?RelevantTaskRecordNo", fMISTaskRecordEntity.RelevantTaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?CurrentlyStep", fMISTaskRecordEntity.CurrentlyStep, DbType.Int32, size: 10);
            parameters.Add("?CompanyId", fMISTaskRecordEntity.CompanyId, DbType.Int32, size: 10);
            return await this.sqlCommand.ExecuteAsync(sql, parameters) > 0;
        }

        public async Task<FMISTaskRecordEntity> GetEntity(long taskRecordId)
        {
            string sql = string.Format("{0} WHERE A.TaskRecordId=?TaskRecordId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskRecordId", taskRecordId, DbType.Int64, size: 20);
            return await this.sqlCommand.QueryFirstOrDefaultAsync<FMISTaskRecordEntity>(sql, parameters);
        }
        public async Task<FMISTaskRecordEntity> GetLatestRecord(long workflowActivityInstanceId)
        {
            string sql = string.Format("{0} WHERE B.WorkflowActivityInstanceId=?WorkflowActivityInstanceId ORDER BY CreateTime DESC LIMIT 1", QUERY_LIST);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkflowActivityInstanceId", workflowActivityInstanceId, DbType.Int64, size: 20);
            return await this.sqlCommand.QueryFirstOrDefaultAsync<FMISTaskRecordEntity>(sql, parameters);
        }

        public async Task<FMISTaskRecordEntity> GetEntity(string taskRecordNo)
        {
            string sql = string.Format("{0} WHERE B.TaskRecordNo=?TaskRecordNo", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskRecordNo", taskRecordNo, DbType.AnsiString, size: 50);
            return await this.sqlCommand.QueryFirstOrDefaultAsync<FMISTaskRecordEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<FMISTaskRecordEntity>, int)> Query(long? executerAdminId, DateTime? date, DateTime? startDate,
            DateTime? endDate, OATaskHandleStatus? handleStatus, FMISTaskType? taskType,bool? isException, string sortName, int? companyId,
            bool? ascending, string taskExecuterFullName, string saleContractNo,string purchaseContractNo,string vehicleBrandName,
            string buyerName,int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>()
            {
                "B.RoleType = 'Admin'"
            };
            DynamicParameters parameters = new DynamicParameters();
            if (companyId.HasValue)
            {
                wheres.Add("A.CompanyId =?CompanyId");
                parameters.Add("CompanyId", companyId.Value, DbType.Int32, size: 20);
            }
            else if (!startDate.HasValue && !endDate.HasValue)
            {
                wheres.Add("B.StartTime<=DATE(NOW())");
            }
            if (!string.IsNullOrWhiteSpace(buyerName))
            {
                wheres.Add("D.BuyerName = ?BuyerName");
                parameters.Add("?BuyerName", buyerName, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(vehicleBrandName))
            {
                wheres.Add("( E.VehicleBrandName = ?VehicleBrandName || F.VehicleBrandName=?VehicleBrandName )");
                parameters.Add("?VehicleBrandName", vehicleBrandName, DbType.AnsiString);
            }
            if (startDate.HasValue)
            {
                wheres.Add("B.StartTime >= ?StartDate");
                parameters.Add("?StartDate", startDate.Value, DbType.Date);
            }
            if (endDate.HasValue)
            {
                wheres.Add("B.StartTime < ?EndDate");
                parameters.Add("?EndDate", endDate.Value.AddDays(1), DbType.Date);
            }
            if (executerAdminId.HasValue)
            {
                wheres.Add("B.TaskExecuterAdminId=?TaskExecuterAdminId");
                parameters.Add("?TaskExecuterAdminId", executerAdminId, DbType.Int64, size: 20);
            }
            if (isException.HasValue && isException.Value)
            {
                wheres.Add("A.IsException=?IsException");
                parameters.Add("?IsException", isException.Value, DbType.Boolean);
            }
            else
            {
                if (handleStatus.HasValue)
                {
                    wheres.Add("B.HandleStatus=?HandleStatus");
                    parameters.Add("?HandleStatus", handleStatus.ToString(), DbType.AnsiString);
                }
            }
            if (taskType.HasValue)
            {
                wheres.Add("A.TaskType=?TaskType");
                parameters.Add("?TaskType", taskType.Value.ToString(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(taskExecuterFullName))
            {
                wheres.Add("B.TaskExecuterFullName=?TaskExecuterFullName");
                parameters.Add("?TaskExecuterFullName", taskExecuterFullName, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(saleContractNo))
            {
                wheres.Add("A.SaleContractNo = ?SaleContractNo");
                parameters.Add("?SaleContractNo", saleContractNo, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(purchaseContractNo))
            {
                wheres.Add("A.PurchaseContractNo = ?PurchaseContractNo");
                parameters.Add("?PurchaseContractNo", purchaseContractNo, DbType.AnsiString);
            }
            //因为转现金任务也写记录到FMIS_TaskRecord，现将转现金审批列表单独出去，所以这里先过滤掉不查询转现金的任务
            wheres.Add("A.BusinessType <> 'WorkflowBusiness_VehicleFeeTransferCashReceipt_VehicleFee'");
            string sql = string.Format(@"SELECT A.TaskRecordId,A.WorkflowActivityInstanceId,A.BusinessTypeName,A.BusinessType,A.TaskType,A.TaskTypeName,A.ReceivableAmount,A.PayableAmount,A.ActualReceiptAmount,A.ActualPaidAmount,
                        A.IsException,A.ExceptionDescription,A.HandledRemark,A.SaleContractNo,A.PurchaseContractNo,A.SaleUniqueCode,B.TaskRecordNo,A.CompanyId,
                        B.TaskCategory,B.HandleStatus,B.PauseTime,B.PauseRemark,B.RelevantTaskRecordNo,B.CurrentlyStep,B.CreateTime,B.TaskExecuterAdminId,
                        B.TaskExecuterFullName,B.StartTime,B.CompleteTime,B.TaskRemark,C.StepType,A.PurchaseUniqueCode,A.CapitalPoolWithdrawId,A.CapitalDeductionAmount,
                        D.BuyerName,A.DepositDeductionAmount,A.RebateDeductionAmount,A.IsAllDeducted
                        FROM FMIS_TaskRecord A
                        INNER JOIN OA_TaskRecord B ON A.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId
                        INNER JOIN OA_WorkflowActivityStepInstance C ON C.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId AND C.TaskStep=B.CurrentlyStep
                        LEFT JOIN SCM_VehicleSaleContract D ON A.SaleContractNo = D.VehicleSaleContractNo 
                        LEFT JOIN SCM_VehicleSaleCheckList E ON A.SaleContractNo = E.VehicleSaleContractNo
                        LEFT  JOIN scm_vehiclepurchasechecklist F ON E.SaleUniqueCode=F.PurchaseUniqueCode
                         {0} ORDER BY {1} {2} LIMIT {3},{4}; 
                    SELECT COUNT(1) 
                    FROM  FMIS_TaskRecord A
                    INNER JOIN OA_TaskRecord B ON A.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId
                    INNER JOIN OA_WorkflowActivityStepInstance C ON C.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId AND C.TaskStep=B.CurrentlyStep
                    LEFT JOIN SCM_VehicleSaleContract D ON A.SaleContractNo = D.VehicleSaleContractNo 
                    LEFT JOIN SCM_VehicleSaleCheckList E ON A.SaleContractNo = E.VehicleSaleContractNo
                    LEFT  JOIN scm_vehiclepurchasechecklist F ON E.SaleUniqueCode=F.PurchaseUniqueCode                    
                    {0}",
                    wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                    !string.IsNullOrWhiteSpace(sortName) ? sortName : "B.CreateTime",
                    ascending.HasValue ? "ASC" : "DESC",
                    (pageIndex - 1) * pageSize,
                    pageSize);
            return await this.sqlCommand.QueryPageableListAsync<FMISTaskRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<FMISTaskRecordEntity>> Get(string saleContractNo)
        {
            string sql = string.Format("{0} WHERE A.SaleContractNo=?SaleContractNo", QUERY_LIST);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SaleContractNo", saleContractNo, DbType.AnsiString, size: 50);
            return await this.sqlCommand.QueryAsync<FMISTaskRecordEntity>(sql, parameters);
        }
        public async Task<IEnumerable<FMISTaskRecordEntity>> Get(long workflowActivityInstanceId)
        {
            string sql = string.Format("{0} WHERE C.WorkflowActivityInstanceId=?WorkflowActivityInstanceId", QUERY_LIST);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkflowActivityInstanceId", workflowActivityInstanceId, DbType.Int64, size: 20);
            return await this.sqlCommand.QueryAsync<FMISTaskRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<FMISTaskRecordEntity>> GetWithCurrentlyStep(long workflowActivityInstanceId)
        {
            string sql = string.Format("{0} WHERE C.WorkflowActivityInstanceId=?WorkflowActivityInstanceId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkflowActivityInstanceId", workflowActivityInstanceId, DbType.Int64, size: 20);
            return await this.sqlCommand.QueryAsync<FMISTaskRecordEntity>(sql, parameters);
        }
        public async Task<IEnumerable<FMISTaskRecordEntity>> GetNoAssignedTaskRecords()
        {
            string sql = string.Format("{0} WHERE B.TaskExecuterAdminId IS NULL", QUERY_LIST);
            return await this.sqlCommand.QueryAsync<FMISTaskRecordEntity>(sql);
        }
        public async Task<bool> UpdateStartTime(FMISTaskRecordEntity taskRecordEntity)
        {
            string sql = this.sqlCommand.GetUpdateSql("FMIS_TaskRecord", new string[] { "ReceivableAmount", "PayableAmount" },
                new string[] { "TaskRecordId" });
            sql = string.Format("{0} {1}", sql, this.sqlCommand.GetUpdateSql("OA_TaskRecord", new string[] { "StartTime", "TaskExecuterAdminId", "TaskExecuterFullName" },
                new string[] { "TaskRecordNo" }));
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskRecordId", taskRecordEntity.TaskRecordId, DbType.Int64, size: 20);
            parameters.Add("?TaskRecordNo", taskRecordEntity.TaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?StartTime", taskRecordEntity.StartTime, DbType.DateTime);
            parameters.Add("?ReceivableAmount", taskRecordEntity.ReceivableAmount, DbType.Decimal);
            parameters.Add("?PayableAmount", taskRecordEntity.PayableAmount, DbType.Decimal);
            parameters.Add("?TaskExecuterAdminId", taskRecordEntity.TaskExecuterAdminId, DbType.Int64, size: 20);
            parameters.Add("?TaskExecuterFullName", taskRecordEntity.TaskExecuterFullName, DbType.AnsiString, size: 20);
            return await this.sqlCommand.ExecuteAsync(sql, parameters) > 0;
        }

        public async Task<bool> UpdateStartTime(string taskRecordNo, DateTime? startTime)
        {
            string sql = this.sqlCommand.GetUpdateSql("OA_TaskRecord", new string[] { "StartTime" }, new string[] { "TaskRecordNo" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskRecordNo", taskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?StartTime", startTime.HasValue ? startTime.Value : DateTime.Now, DbType.DateTime);
            return await this.sqlCommand.ExecuteAsync(sql, parameters) > 0;
        }
        public async Task<bool> UpdateExecuterAdminInfo(FMISTaskRecordEntity taskRecordEntity)
        {
            string sql = this.sqlCommand.GetUpdateSql("OA_TaskRecord", new string[] { "TaskExecuterAdminId", "TaskExecuterFullName" }, new string[] { "TaskRecordNo" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskRecordNo", taskRecordEntity.TaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?TaskExecuterAdminId", taskRecordEntity.TaskExecuterAdminId, DbType.Int64, size: 20);
            parameters.Add("?TaskExecuterFullName", taskRecordEntity.TaskExecuterFullName, DbType.AnsiString, size: 20);
            return await this.sqlCommand.ExecuteAsync(sql, parameters) > 0;
        }

        public async Task<FMISTaskRecordCountEntity> TodayRecordCount(long? executerAdminId)
        {
            string sql = @"SELECT SUM(PendingCount) AS PendingCount,
                            SUM(HandledCount) AS HandledCount,
                            SUM(RejectedCount) AS RejectedCount,
                            SUM(PausedCount) AS PausedCount,
                            SUM(ClosedCount) AS ClosedCount
                            FROM (
                                SELECT CASE WHEN HandleStatus='Pending' THEN COUNT(HandleStatus) ELSE 0 END AS PendingCount,
                                CASE WHEN HandleStatus='Handled' THEN COUNT(HandleStatus) ELSE 0 END AS HandledCount,
                                CASE WHEN HandleStatus='Rejected' THEN COUNT(HandleStatus) ELSE 0 END AS RejectedCount,
                                CASE WHEN HandleStatus='Paused' THEN COUNT(HandleStatus) ELSE 0 END AS PausedCount,
                                CASE WHEN HandleStatus='Closed' THEN COUNT(HandleStatus) ELSE 0 END AS ClosedCount
                                FROM FMIS_TaskRecord A
                                INNER JOIN OA_TaskRecord B ON A.TaskRecordNo=B.TaskRecordNo
                                WHERE B.TaskExecuterAdminId=?TaskExecuterAdminId AND B.StartTime=?StartTime
                                GROUP BY HandleStatus
                            ) T";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskExecuterAdminId", executerAdminId, DbType.Int64, size: 20);
            return await this.sqlCommand.QueryFirstOrDefaultAsync<FMISTaskRecordCountEntity>(sql, parameters);
        }

        public async Task<FMISTaskRecordCountEntity> UnfinishedCount(long? executerAdminId)
        {
            string sql = @"SELECT SUM(PendingCount) AS PendingCount,
                            SUM(HandledCount) AS HandledCount,
                            SUM(RejectedCount) AS RejectedCount,
                            SUM(PausedCount) AS PausedCount,
                            SUM(ClosedCount) AS ClosedCount FROM
                            (
                                SELECT CASE WHEN HandleStatus='Pending' THEN COUNT(HandleStatus) ELSE 0 END AS PendingCount,
                                CASE WHEN HandleStatus='Handled' THEN 0 ELSE 0 END AS HandledCount,
                                CASE WHEN HandleStatus='Rejected' THEN COUNT(HandleStatus) ELSE 0 END AS RejectedCount,
                                CASE WHEN HandleStatus='Paused' THEN COUNT(HandleStatus) ELSE 0 END AS PausedCount,
                                CASE WHEN HandleStatus='Closed' THEN 0 ELSE 0 END AS ClosedCount
                                FROM FMIS_TaskRecord A
                                INNER JOIN OA_TaskRecord B ON A.TaskRecordNo=B.TaskRecordNo
                                WHERE B.TaskExecuterAdminId=?TaskExecuterAdminId AND B.StartTime<date(now()) AND B.CompleteTime IS NULL
                                GROUP BY HandleStatus
                            ) T";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskExecuterAdminId", executerAdminId, DbType.Int64, size: 20);
            return await this.sqlCommand.QueryFirstOrDefaultAsync<FMISTaskRecordCountEntity>(sql, parameters);
        }

        public Task<FMISTaskRecordCountEntity> ExceptionCount(long? executerAdminId)
        {
            throw new NotImplementedException();
        }


        public async Task<bool> HandleTaskException(IEnumerable<FMISTaskRecordEntity> taskRecordEntities)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> dynamicParameters = new List<DynamicParameters>();
            string sql = this.sqlCommand.GetUpdateSql("FMIS_TaskRecord", new string[] { "IsException", "HandledRemark" },
                new string[] { "TaskRecordId" });
            foreach (var entity in taskRecordEntities)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?IsException", entity.IsException, DbType.Boolean);
                parameters.Add("?HandledRemark", entity.HandledRemark, DbType.AnsiString, size: 40);
                parameters.Add("?TaskRecordId", entity.TaskRecordId, DbType.Int64, size: 20);
                sqls.Add(sql);
                dynamicParameters.Add(parameters);
            }
            return await this.sqlCommand.TransactionExecuteAsync(sqls, dynamicParameters) > 0;
        }

        public async Task<bool> UpdateTaskException(IEnumerable<FMISTaskRecordEntity> taskRecordEntities)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> dynamicParameters = new List<DynamicParameters>();
            string sql = this.sqlCommand.GetUpdateSql("FMIS_TaskRecord", new string[] { "IsException", "ExceptionDescription", "ActualReceiptAmount", "ActualPaidAmount", "HandledRemark" },
                new string[] { "TaskRecordId" });
            foreach (var entity in taskRecordEntities)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?IsException", entity.IsException, DbType.Boolean);
                parameters.Add("?ExceptionDescription", entity.ExceptionDescription, DbType.AnsiString, size: 400);
                parameters.Add("?ActualReceiptAmount", entity.ActualReceiptAmount, DbType.Decimal);
                parameters.Add("?ActualPaidAmount", entity.ActualPaidAmount, DbType.Decimal);
                parameters.Add("?HandledRemark", entity.HandledRemark, DbType.AnsiString, size: 40);
                parameters.Add("?TaskRecordId", entity.TaskRecordId, DbType.Int64, size: 20);
                sqls.Add(sql);
                dynamicParameters.Add(parameters);
            }
            return await this.sqlCommand.TransactionExecuteAsync(sqls, dynamicParameters) > 0;
        }

        public async Task<bool> UpdateReceivableReceiptPaymentAmount(IEnumerable<FMISTaskRecordEntity> taskRecordEntities)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> dynamicParameters = new List<DynamicParameters>();
            string sql = this.sqlCommand.GetUpdateSql("FMIS_TaskRecord", new string[] { "ReceivableAmount", "PayableAmount", "HandledRemark" },
                new string[] { "TaskRecordId" });
            foreach (var entity in taskRecordEntities)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?ReceivableAmount", entity.ReceivableAmount, DbType.Decimal);
                parameters.Add("?PayableAmount", entity.PayableAmount, DbType.Decimal);
                parameters.Add("?HandledRemark", entity.HandledRemark, DbType.AnsiString, size: 40);
                parameters.Add("?TaskRecordId", entity.TaskRecordId, DbType.Int64, size: 20);
                sqls.Add(sql);
                dynamicParameters.Add(parameters);
            }
            return await this.sqlCommand.TransactionExecuteAsync(sqls, dynamicParameters) > 0;
        }

        public async Task<bool> UpdateActualReceiptPaymentAmount(IEnumerable<FMISTaskRecordEntity> taskRecordEntities)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> dynamicParameters = new List<DynamicParameters>();
            string sql = this.sqlCommand.GetUpdateSql("FMIS_TaskRecord", new string[] { "ActualReceiptAmount", "ActualPaidAmount", "HandledRemark" },
                new string[] { "TaskRecordId" });
            foreach (var entity in taskRecordEntities)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?ActualReceiptAmount", entity.ActualReceiptAmount, DbType.Decimal);
                parameters.Add("?ActualPaidAmount", entity.ActualPaidAmount, DbType.Decimal);
                parameters.Add("?HandledRemark", entity.HandledRemark, DbType.AnsiString, size: 40);
                parameters.Add("?TaskRecordId", entity.TaskRecordId, DbType.Int64, size: 20);
                sqls.Add(sql);
                dynamicParameters.Add(parameters);
            }
            return await this.sqlCommand.TransactionExecuteAsync(sqls, dynamicParameters) > 0;
        }

        /// <summary>
        /// 新增任务抄送人信息
        /// </summary>
        /// <param name="taskReceivers">抄送人</param>
        /// <returns></returns>
        public async Task<bool> InsertTaskReceiverAsync(List<TaskReceiverRecordEntity> taskReceivers)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> dynamicParameters = new List<DynamicParameters>();
            sqls.AddRange(this.GetInsertTaskReceiverRecordSql(taskReceivers, ref dynamicParameters));
            return await this.sqlCommand.TransactionExecuteAsync(sqls, dynamicParameters) > 0;
        }

        public async Task<bool> UpdateActualReceiptAmount(long workflowActivityInstanceId, decimal actualReceiptAmount)
        {
            string sql = this.sqlCommand.GetUpdateSql("FMIS_TaskRecord",
                new string[] { "ActualReceiptAmount" }, new string[] { "WorkflowActivityInstanceId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ActualReceiptAmount", actualReceiptAmount, DbType.Decimal);
            parameters.Add("?WorkflowActivityInstanceId", workflowActivityInstanceId, DbType.Int64);
            return await this.sqlCommand.ExecuteAsync(sql, parameters) > 0;
        }

        public async Task<(IEnumerable<FMISTaskRecordEntity>, int)> QueryVehiclePurchaseListAsync(long? executerAdminId,
            DateTime? date, DateTime? startDate, DateTime? endDate, OATaskHandleStatus? handleStatus, FMISTaskType? taskType,
            bool? isException, string sortName, int? companyId, bool? ascending, string taskExecuterFullName,
            string purchaseContractNo, string vehicleBrandName, VehiclePurchaseContractStatus? vehiclePurchaseContractStatus,
            int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>()
            {
                "B.RoleType = 'Admin'"
            };
            DynamicParameters parameters = new DynamicParameters();
            if (companyId.HasValue)
            {
                wheres.Add("A.CompanyId =?CompanyId");
                parameters.Add("CompanyId", companyId.Value, DbType.Int32, size: 20);
            }
            if (date.HasValue)
            {
                wheres.Add("B.StartTime=?StartTime");
                parameters.Add("?StartTime", date, DbType.Date);
            }
            if (startDate.HasValue)
            {
                wheres.Add("B.StartTime >= ?StartDate");
                parameters.Add("?StartDate", startDate.Value, DbType.Date);
            }
            if (endDate.HasValue)
            {
                wheres.Add("B.StartTime < ?EndDate");
                parameters.Add("?EndDate", endDate.Value.AddDays(1), DbType.Date);
            }
            if (executerAdminId.HasValue)
            {
                wheres.Add("B.TaskExecuterAdminId=?TaskExecuterAdminId");
                parameters.Add("?TaskExecuterAdminId", executerAdminId, DbType.Int64, size: 20);
            }
            if (isException.HasValue && isException.Value)
            {
                wheres.Add("A.IsException=?IsException");
                parameters.Add("?IsException", isException.Value, DbType.Boolean);
            }
            else
            {
                if (handleStatus.HasValue)
                {
                    wheres.Add("B.HandleStatus=?HandleStatus");
                    parameters.Add("?HandleStatus", handleStatus.ToString(), DbType.AnsiString);
                }
            }
            if (taskType.HasValue)
            {
                wheres.Add("A.TaskType=?TaskType");
                parameters.Add("?TaskType", taskType.Value.ToString(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(taskExecuterFullName))
            {
                wheres.Add("B.TaskExecuterFullName=?TaskExecuterFullName");
                parameters.Add("?TaskExecuterFullName", taskExecuterFullName, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(purchaseContractNo))
            {
                wheres.Add("A.PurchaseContractNo = ?PurchaseContractNo");
                parameters.Add("?PurchaseContractNo", purchaseContractNo, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(vehicleBrandName))
            {
                wheres.Add("E.VehicleBrandName = ?VehicleBrandName");
                parameters.Add("?VehicleBrandName", vehicleBrandName, DbType.AnsiString);
            }
            if (vehiclePurchaseContractStatus.HasValue)
            {
                wheres.Add("D.VehiclePurchaseContractStatus = ?VehiclePurchaseContractStatus");
                parameters.Add("?VehiclePurchaseContractStatus", vehiclePurchaseContractStatus.Value.ToString(), DbType.AnsiString);
            }
            //因为转现金任务也写记录到FMIS_TaskRecord，现将转现金审批列表单独出去，所以这里先过滤掉不查询转现金的任务
            wheres.Add("A.BusinessType <> 'WorkflowBusiness_VehicleFeeTransferCashReceipt_VehicleFee'");
            string sql = string.Format(@"SELECT A.TaskRecordId,A.WorkflowActivityInstanceId,A.BusinessTypeName,A.BusinessType,A.TaskType,A.TaskTypeName,A.ReceivableAmount,A.PayableAmount,A.ActualReceiptAmount,A.ActualPaidAmount,
                        A.IsException,A.ExceptionDescription,A.HandledRemark,A.SaleContractNo,A.PurchaseContractNo,A.SaleUniqueCode,B.TaskRecordNo,A.CompanyId,
                        B.TaskCategory,B.HandleStatus,B.PauseTime,B.PauseRemark,B.RelevantTaskRecordNo,B.CurrentlyStep,B.CreateTime,B.TaskExecuterAdminId,
                        B.TaskExecuterFullName,B.StartTime,B.CompleteTime,B.TaskRemark,C.StepType,A.PurchaseUniqueCode,A.CapitalPoolWithdrawId,A.CapitalDeductionAmount,
                        A.DepositDeductionAmount,A.RebateDeductionAmount
                        FROM FMIS_TaskRecord A
                        INNER JOIN OA_TaskRecord B ON A.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId
                        INNER JOIN OA_WorkflowActivityStepInstance C ON C.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId AND C.TaskStep=B.CurrentlyStep
                        INNER JOIN SCM_VehiclePurchaseContract D ON A.PurchaseContractNo = D.VehiclePurchaseContractNo 
                        INNER JOIN SCM_VehiclePurchaseCheckList E ON A.PurchaseContractNo = E.VehiclePurchaseContractNo
                        {0} ORDER BY {1} {2} LIMIT {3},{4};
                        SELECT COUNT(1) 
                        FROM FMIS_TaskRecord A
                        INNER JOIN OA_TaskRecord B ON A.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId
                        INNER JOIN OA_WorkflowActivityStepInstance C ON C.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId AND C.TaskStep=B.CurrentlyStep
                        INNER JOIN SCM_VehiclePurchaseContract D ON A.PurchaseContractNo = D.VehiclePurchaseContractNo 
                        INNER JOIN SCM_VehiclePurchaseCheckList E ON A.PurchaseContractNo = E.VehiclePurchaseContractNo
                        {0}",
                        wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                        !string.IsNullOrWhiteSpace(sortName) ? sortName : "B.CreateTime",
                        ascending.HasValue ? "ASC" : "DESC",
                        (pageIndex - 1) * pageSize,
                        pageSize);
            return await this.sqlCommand.QueryPageableListAsync<FMISTaskRecordEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<FMISTaskRecordEntity>, int)> QueryVehicleSaleListAsync(long? executerAdminId, DateTime? date, DateTime? startDate, DateTime? endDate, OATaskHandleStatus? handleStatus, FMISTaskType? taskType, bool? isException, string sortName, int? companyId, bool? ascending, string taskExecuterFullName,
            string saleContractNo, string vehicleBrandName, string buyerName, VehicleSaleCheckListStatus? checkListStatus, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>()
            {
                "B.RoleType = 'Admin'"
            };
            DynamicParameters parameters = new DynamicParameters();
            if (companyId.HasValue)
            {
                wheres.Add("A.CompanyId =?CompanyId");
                parameters.Add("CompanyId", companyId.Value, DbType.Int32, size: 20);
            }
            if (date.HasValue)
            {
                wheres.Add("(B.StartTime=?StartTime");
                parameters.Add("?StartTime", date, DbType.Date);
            }
            if (startDate.HasValue)
            {
                wheres.Add("B.StartTime >= ?StartDate");
                parameters.Add("?StartDate", startDate.Value, DbType.Date);
            }
            if (endDate.HasValue)
            {
                wheres.Add("B.StartTime < ?EndDate");
                parameters.Add("?EndDate", endDate.Value.AddDays(1), DbType.Date);
            }
            if (executerAdminId.HasValue)
            {
                wheres.Add("B.TaskExecuterAdminId=?TaskExecuterAdminId");
                parameters.Add("?TaskExecuterAdminId", executerAdminId, DbType.Int64, size: 20);
            }
            if (isException.HasValue && isException.Value)
            {
                wheres.Add("A.IsException=?IsException");
                parameters.Add("?IsException", isException.Value, DbType.Boolean);
            }
            else
            {
                if (handleStatus.HasValue)
                {
                    wheres.Add("B.HandleStatus=?HandleStatus");
                    parameters.Add("?HandleStatus", handleStatus.ToString(), DbType.AnsiString);
                }
            }
            if (taskType.HasValue)
            {
                wheres.Add("A.TaskType=?TaskType");
                parameters.Add("?TaskType", taskType.Value.ToString(), DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(taskExecuterFullName))
            {
                wheres.Add("B.TaskExecuterFullName=?TaskExecuterFullName");
                parameters.Add("?TaskExecuterFullName", taskExecuterFullName, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(saleContractNo))
            {
                wheres.Add("A.SaleContractNo = ?SaleContractNo");
                parameters.Add("?SaleContractNo", saleContractNo, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(vehicleBrandName))
            {
                wheres.Add("E.VehicleBrandName = ?VehicleBrandName");
                parameters.Add("?VehicleBrandName", vehicleBrandName, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(buyerName))
            {
                wheres.Add("D.BuyerName = ?BuyerName");
                parameters.Add("?BuyerName", buyerName, DbType.AnsiString);
            }
            if (checkListStatus.HasValue)
            {
                wheres.Add("E.CheckListStatus = ?CheckListStatus");
                parameters.Add("?CheckListStatus", checkListStatus.Value.ToString(), DbType.AnsiString);
            }
            //因为转现金任务也写记录到FMIS_TaskRecord，现将转现金审批列表单独出去，所以这里先过滤掉不查询转现金的任务
            wheres.Add("A.BusinessType <> 'WorkflowBusiness_VehicleFeeTransferCashReceipt_VehicleFee'");
            string sql = string.Format(@"SELECT A.TaskRecordId,A.WorkflowActivityInstanceId,A.BusinessTypeName,A.BusinessType,A.TaskType,A.TaskTypeName,A.ReceivableAmount,A.PayableAmount,A.ActualReceiptAmount,A.ActualPaidAmount,
                        A.IsException,A.ExceptionDescription,A.HandledRemark,A.SaleContractNo,A.PurchaseContractNo,A.SaleUniqueCode,B.TaskRecordNo,A.CompanyId,
                        B.TaskCategory,B.HandleStatus,B.PauseTime,B.PauseRemark,B.RelevantTaskRecordNo,B.CurrentlyStep,B.CreateTime,B.TaskExecuterAdminId,
                        B.TaskExecuterFullName,B.StartTime,B.CompleteTime,B.TaskRemark,C.StepType,A.PurchaseUniqueCode,A.CapitalPoolWithdrawId,A.CapitalDeductionAmount,
                        D.BuyerName,A.DepositDeductionAmount,A.RebateDeductionAmount
                        FROM FMIS_TaskRecord A
                        INNER JOIN OA_TaskRecord B ON A.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId
                        INNER JOIN OA_WorkflowActivityStepInstance C ON C.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId AND C.TaskStep=B.CurrentlyStep
                        INNER JOIN SCM_VehicleSaleContract D ON A.SaleContractNo = D.VehicleSaleContractNo 
                        INNER JOIN SCM_VehicleSaleCheckList E ON A.SaleContractNo = E.VehicleSaleContractNo
                        {0} ORDER BY {1} {2} LIMIT {3},{4};
                        SELECT COUNT(1) 
                        FROM FMIS_TaskRecord A
                        INNER JOIN OA_TaskRecord B ON A.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId
                        INNER JOIN OA_WorkflowActivityStepInstance C ON C.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId AND C.TaskStep=B.CurrentlyStep
                        INNER JOIN SCM_VehicleSaleContract D ON A.SaleContractNo = D.VehicleSaleContractNo 
                        INNER JOIN SCM_VehicleSaleCheckList E ON A.SaleContractNo = E.VehicleSaleContractNo
                        {0}",
                        wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
                        !string.IsNullOrWhiteSpace(sortName) ? sortName : "B.CreateTime",
                        ascending.HasValue ? "ASC" : "DESC",
                        (pageIndex - 1) * pageSize,
                        pageSize);
            return await this.sqlCommand.QueryPageableListAsync<FMISTaskRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<FMISTaskRecordEntity>> GetListByWorkflowActivityInstanceId(long workflowActivityInstanceId)
        {
            string sql = string.Format("{0} WHERE A.WorkflowActivityInstanceId = ?WorkflowActivityInstanceId", QUERY_ENTITY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkflowActivityInstanceId", workflowActivityInstanceId, DbType.Int64, size: 20);
            return await this.sqlCommand.QueryAsync<FMISTaskRecordEntity>(sql, parameters);
        }

        public async Task<int> UpdateReceivableReceiptPaymentAmount(FMISTaskRecordEntity taskRecord)
        {
            string sql = this.database.GetUpdateSql("FMIS_TaskRecord", new string[] { "ReceivableAmount", "PayableAmount", "HandledRemark", "CapitalDeductionAmount", "RebateDeductionAmount", "DepositDeductionAmount" },
                new string[] { "TaskRecordId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ReceivableAmount", taskRecord.ReceivableAmount, DbType.Decimal);
            parameters.Add("?PayableAmount", taskRecord.PayableAmount, DbType.Decimal);
            parameters.Add("?HandledRemark", taskRecord.HandledRemark, DbType.AnsiString, size: 40);
            parameters.Add("?CapitalDeductionAmount", taskRecord.CapitalDeductionAmount, DbType.Decimal);
            parameters.Add("?RebateDeductionAmount", taskRecord.RebateDeductionAmount, DbType.Decimal);
            parameters.Add("?DepositDeductionAmount", taskRecord.DepositDeductionAmount, DbType.Decimal);
            parameters.Add("?TaskRecordId", taskRecord.TaskRecordId, DbType.Int64, size: 20);
            return await database.Master.ExecuteAsync(sql, parameters, database.Transaction);
        }

        public async Task<IEnumerable<FMISTaskRecordEntity>> GetBySaleContractNoAsync(string vehicleSaleContractNo)
        {
            return await database.Slave.QueryAsync<FMISTaskRecordEntity>($"{QUERY_ENTITY} WHERE A.SaleContractNo = ?SaleContractNo ORDER BY A.TaskRecordId", new { SaleContractNo = vehicleSaleContractNo });
        }
    }
}
